﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class HisSpouseDAL : IHisSpouseDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HisSpouseDAL()
        {

        }
        public DataTable ListHisSpousesDataTable(HisSpouseInfo hisInfo)
        {
            string strSQL;
            DataTable dtb = null;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            List<HisSpouseInfo> infoList = new List<HisSpouseInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT p.SPOUSE_SEQ, p.EMP_SEQ, p.NAME_PREFIX_SEQ"
                        +", p.SPOUSE_NAME, p.SPOUSE_LAST_NAME, p.SPOUSE_STATUS"
                        +", empnp.PREFIX_name as emp_PREFIX_name"
                        +", emp.first_name, emp.last_name"
                        + ", CONCAT(p.SPOUSE_NAME,' ',p.SPOUSE_LAST_NAME) AS spname"
                        + ", CASE p.earning_status "
                        + " WHEN '0' THEN 'ไม่มีรายได้' "
                        + " WHEN '1' THEN 'มีรายได้' "
                        + " END AS earning, p.earning_status"
                        +", CASE p.SPOUSE_STATUS "
                        +" WHEN '1' THEN 'อยู่ด้วยกัน' "
                        +" WHEN '2' THEN 'หย่าร้าง' "
                        +" WHEN '3' THEN 'เสียชีวิต' "
                        +" ELSE 'อื่นๆ' "
                        + " END AS SPOUSE_STATUS_NAME"
                        + ", p.CITIZEN, p.place_of_birth"
                        +" FROM HRM_PS_EMP_SPOUSES p"
                        +" LEFT JOIN hrm_ps_employees emp ON p.emp_seq = emp.emp_seq "
                        +" LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                        + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                        + (hisInfo.Sequence > 0 ? " AND p.SPOUSE_SEQ = @Sequence" : "")
                        + (empSeq > 0 ? " AND p.EMP_SEQ = @empSequence" : "");
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


                //IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                //while (rdr.Read())
                //{
                //    EmployeeDAL empDAL = new EmployeeDAL();
                //    EmployeeInfo empInfo = new EmployeeInfo();
                //    if (!rdr.IsDBNull(1))
                //    {
                //        IList<EmployeeInfo> empList = empDAL.GetEmployees(new hrm_ps_employees { emp_seq = rdr.GetInt32(1) }};// empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                //        empInfo = empList[0];
                //    }

                //    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                //    MasNamePrefixInfo pfInfo = new MasNamePrefixInfo();
                //    if (!rdr.IsDBNull(2))
                //    {
                //        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(2)));
                //        pfInfo = pfList[0];
                //    }

                //    PersonInfo personInfo = new PersonInfo();
                //    personInfo.FirstName = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                //    personInfo.LastName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                //    personInfo.NamePrefix = pfInfo;
                //    personInfo.FullName = pfInfo.Name + rdr.GetString(11);
                //    personInfo.Citizen = rdr.IsDBNull(12) ? "" : rdr.GetString(12);

                //    HisSpouseInfo Info = new HisSpouseInfo(rdr.GetInt32(0)
                //            , empInfo
                //            , personInfo
                //            , rdr.GetString(7) == "1"
                //            , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                //            , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                //            , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                //            , rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9)
                //            , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10));
                //    infoList.Add(Info);
                //}
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                // dbManager.CloseReader();
                dbManager.Dispose();
            }
            return dtb;
        }

        public IList<HisSpouseInfo> ListHisSpouses(HisSpouseInfo hisInfo)
        {
            string strSQL;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            List<HisSpouseInfo> infoList = new List<HisSpouseInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT p.SPOUSE_SEQ, p.EMP_SEQ, p.NAME_PREFIX_SEQ"
                        + ", p.SPOUSE_NAME, p.SPOUSE_LAST_NAME, p.SPOUSE_STATUS"
                        + ", CASE p.SPOUSE_STATUS "
                                + " WHEN '1' THEN 'อยู่ด้วยกัน'"
                                + " WHEN '2' THEN 'หย่าร้าง'"
                                + " WHEN '3' THEN 'เสียชีวิต'"
                                + " ELSE 'อื่นๆ'"
                         + " END AS SPOUSE_STATUS_NAME"
                        + ", p.EARNING_STATUS, p.RECORD_STATUS, p.CREATED_BY, p.UPDATED_BY"
                        + ", CONCAT(p.SPOUSE_NAME, '  ', p.SPOUSE_LAST_NAME) AS FULL_NAME"
                        + ", p.CITIZEN, p.place_of_birth"
                    + " FROM HRM_PS_EMP_SPOUSES p"
                    + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                    + (hisInfo.Sequence > 0 ? " AND p.SPOUSE_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND p.EMP_SEQ = @empSequence" : "");
                //dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                        empInfo = empList[0];
                    }

                    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo pfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(2)));
                        pfInfo = pfList[0];
                    }

                    PersonInfo personInfo = new PersonInfo();
                    personInfo.FirstName = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                    personInfo.LastName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    personInfo.NamePrefix = pfInfo;
                    personInfo.FullName = pfInfo.Name + rdr.GetString(11);
                    personInfo.Citizen = rdr.IsDBNull(12) ? "" : rdr.GetString(12);

                    HisSpouseInfo Info = new HisSpouseInfo(rdr.GetInt32(0)
                        , empInfo
                        , personInfo
                        , rdr.GetString(7) == "1"
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9)
                        , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10));
                    Info.PlaceOfBirth = rdr.IsDBNull(13) ? "" : rdr.GetString(13);
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }

        public DataSet LoadSpouse(Nullable<int> Sequence, Nullable<int> EmployeeSequence)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@empSequence", EmployeeSequence);

                strSQL = "SELECT p.SPOUSE_SEQ, p.EMP_SEQ, p.NAME_PREFIX_SEQ,"
	                    +" p.SPOUSE_NAME, p.SPOUSE_LAST_NAME, p.SPOUSE_STATUS,"
	                    +" CASE p.SPOUSE_STATUS "
                        +" WHEN '1' THEN "
                        +" 	'อยู่ด้วยกัน' "
                        +" WHEN '2' THEN "
                        +"	'หย่าร้าง' "
                        +" WHEN '3' THEN "
                        +"	'เสียชีวิต' "
                        +" ELSE "
                        +"	'อื่นๆ' "
                        +" END AS SPOUSE_STATUS_NAME, "
                        +" p.EARNING_STATUS,"
                        +" p.RECORD_STATUS, "
                        +" p.CREATED_BY, "
                        +" p.UPDATED_BY, "
                        +" CONCAT( "
                        +"	np.PREFIX_NAME, "
                        +"	p.SPOUSE_NAME, "
                        +"	'  ', "
                        +"	p.SPOUSE_LAST_NAME "
                        +" ) AS FULL_NAME, "
                        + " p.CITIZEN , p.place_of_birth"
                        +" FROM "
                        + " 	HRM_PS_EMP_SPOUSES p "
                        +" LEFT JOIN MAS_NAME_PREFIXES np ON p.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                        + (Sequence.HasValue ? " AND p.SPOUSE_SEQ = @Sequence" : "")
                        + (EmployeeSequence.HasValue ? " AND p.EMP_SEQ = @empSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public void Insert(HisSpouseInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@Name", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@EarningStatus", Info.Earning);
                dbManager.AddParameters(5, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(6, "@Status", Info.Status);
                dbManager.AddParameters(7, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(9, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(10, "@PlaceOfBirth", Info.PlaceOfBirth);

                strSQL = "INSERT INTO HRM_PS_EMP_SPOUSES(EMP_SEQ, NAME_PREFIX_SEQ"
                        + ", SPOUSE_NAME, SPOUSE_LAST_NAME, EARNING_STATUS, SPOUSE_STATUS"
                        + ", CITIZEN, RECORD_STATUS, CREATED_BY, CREATED_WHEN, place_of_birth)"
                    + " VALUES(@EmpSeq, @PrefixSeq, @Name, @LastName, @EarningStatus"
                        + ", @Status, @Citizen, @RecordStatus, @CreatedBy, @CreatedWhen, @PlaceOfBirth)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(HisSpouseInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@Name", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(5, "@EarningStatus", Info.Earning);
                dbManager.AddParameters(6, "@Status", Info.Status);
                dbManager.AddParameters(7, "@Sequence", Info.Sequence);
                dbManager.AddParameters(8, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(9, "@UpdatedWhen", DateTime.Today);
                dbManager.AddParameters(10, "@PlaceOfBirth", Info.PlaceOfBirth);

                strSQL = "UPDATE HRM_PS_EMP_SPOUSES"
                        + " SET EMP_SEQ = @EmpSeq"
                        + ", NAME_PREFIX_SEQ = @PrefixSeq"
                        + ", SPOUSE_NAME = @Name"
                        + ", SPOUSE_LAST_NAME = @LastName"
                        + ", EARNING_STATUS = @EarningStatus"
                        + ", SPOUSE_STATUS = @Status"
                        + ", CITIZEN = @Citizen"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                        + ", place_of_birth = @PlaceOfBirth"
                    + " WHERE SPOUSE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy, string empSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);
                dbManager.AddParameters(4, "@empSequence", empSeq);

                strSQL = "UPDATE HRM_PS_EMP_SPOUSES"
                        + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE emp_seq = @empSequence AND SPOUSE_SEQ = @Sequence ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }
    }
}
